﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using LIB.Data;

/// <summary>
/// Summary description for c_nhapdiem
/// </summary>
public class c_nhapdiem
{

    public static DataTable Fdt_SINHVIEN_DK_TIM(string b_lop, string id_nh, string idmon, string id_hk, string loai)
    {
        LIB.Core.luu.ht_nsd nsd = new LIB.Core.luu.ht_nsd();
        string id_nsd = nsd.id_ns;
        return SqlData.GetData(new object[] { id_nsd, b_lop, id_nh, idmon, id_hk, loai }, "PNHAPDIEM_TIM");
    }
    //Liet ke diem
    public static DataTable Fdt_LKE_DSDIEM(string id_tkb, string b_ma_sv, string b_mon, string hk)
    {
        return SqlData.GetData(new object[] { id_tkb, b_ma_sv, b_mon, hk }, "PTINHDIEM_LKE");
    }
    public static string Fs_DN_DIEM_LKE(string idnam)
    {
        DataTable b_dt = new DataTable();
        b_dt = SqlData.GetData(idnam, "P_DN_DIEM_LKE");
        string b_chuoi = string.Empty;
        return (b_dt.Rows.Count == 0 ? "" : b_dt.Rows[0]["congthuc"].ToString());
    }
    public static object[] Fs_DN_DIEM_NH(string id, string namhoc, string ct)
    {
        object b = "@id";
        return SqlData.ExeCuteProcedure(new object[] { id, namhoc, ct }, b, "P_DN_DIEM_NH");
    }
    //Thêm mơi
    public static void P_MON_NH(string id, string ten)
    {
        SqlData.ExeCuteProcedure(new object[] { id, ten }, "PBO_MON_NH");
    }
    public static void P_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PBO_MON_XOA");
    }
    //nha
    public static DataTable Fdt_NHA_LKE()
    {
        return SqlData.GetData("PNHA_LKE");
    }
    public static void P_NHA_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PNHA_XOA");
    }
    public static string Fs_NHA_NH(string id, string ten)
    {
        object[] b_gtri;
        b_gtri = SqlData.ExeCuteProcedure(new object[] { id, ten }, "@id", "PNHA_NH");
        return LIB.Core.LibConvert.toString(b_gtri[0]);
    }
    //Phong
    public static DataTable Fdt_PHONG_LKE(string id_nha)
    {
        return SqlData.GetData(id_nha, "PPHONG_LKE");
    }
    public static void P_PHONG_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PPHONG_XOA");
    }
    public static string Fs_PHONG_NH(string id, string idnha, string ten, int b_giuong)
    {
        object[] b_gtri;
        b_gtri = SqlData.ExeCuteProcedure(new object[] { id, idnha, ten, b_giuong }, "@id", "PPHONG_NH");
        return LIB.Core.LibConvert.toString(b_gtri[0]);
    }
    //DS TKB
    public static DataTable Fdt_SV_KT_LKE(string idPhong)
    {
        return SqlData.GetData(idPhong, "PDK_KTX_LKE");
    }
    //Đoan này tìm 
    public static DataTable Fdt_SV_TIM(string ma)
    {
        return SqlData.GetData(ma, "PSINHVIEN_TIM_TR");
    }
    //Dang ky phong
    public static void P_PHONG_DK(string id, string idPh, string ma_sv)
    {
        SqlData.ExeCuteProcedure(new object[] { id, idPh, ma_sv }, "PDK_PHONG_NH");
    }
    //Huy dang ky
    public static void P_PHONG_DK_XOA(string id, string phong)
    {
        SqlData.ExeCuteProcedure(new object[] { id, phong }, "P_DK_PHONG_XOA");
    }
    //Thông tin chi tiết nơi ở
    public static DataTable Fdt_DK_PHONG_CT(string idsv)
    {
        return SqlData.GetData(idsv, "PDK_PHONG_TK_SV");
    }
    //Chuyển phòng
    public static void P_PHONG_DK_CHUYEN(string idcu, string phongcu, string idmoi, string pmoi, int ngay_nh, string b_ma, string ndung)
    {
        SqlData.ExeCuteProcedure(new object[] { idcu, phongcu, idmoi, pmoi, b_ma, ngay_nh, ndung }, "PPHONG_DK_CHUYEN");
    }

    #region Ngành đào tạo
    //Thêm môn
    public static void P_NGANH_DT_NH(string id, string id_nganhhoc, string ten)
    {
        // thực thi store và Trả lại chuỗi id_huyen
        SqlData.ExeCuteProcedure(new object[] { id, id_nganhhoc, ten }, "PNGANH_DT_NH");
    }
    //Xóa huyện
    public static void P_NGANH_DT_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PNGANH_DT_XOA");
    }

    #endregion
    #region Môn học
    //Thêm môn
    public static void P_MON_HOC_NH(string id, string id_nganh, string ten)
    {
        // thực thi store và Trả lại chuỗi id_huyen
        SqlData.ExeCuteProcedure(new object[] { id, id_nganh, ten }, "PMON_HOC_NH");
    }
    //Xóa huyện
    public static void P_MON_HOC_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PMON_HOC_XOA");
    }

    #endregion
    #region Tôn giáo
    //Thêm mơi
    public static string P_TON_GIAO_NH(string id, string ten)
    {
        return SqlData.ExeCuteProcedure(new object[] { id, ten }, "@ma_tongiao", "PTON_GIAO_NH")[0].ToString();
    }
    public static void P_TON_GIAO_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PTON_GIAO_XOA");
    }
    #endregion
    #region Dân tộc
    //Thêm mơi
    public static string P_DAN_TOC_NH(string id, string ten)
    {
        return SqlData.ExeCuteProcedure(new object[] { id, ten }, "@ma_dantoc", "PDANTOC_NH")[0].ToString();
    }
    public static void P_DAN_TOC_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PDAN_TOC_XOA");
    }
    #endregion
    #region Huyện
    //Thêm huyện
    public static string P_HUYEN_NH(string id, string ten, string id_tinh)
    {
        // thực thi store và Trả lại chuỗi id_huyen
        return SqlData.ExeCuteProcedure(new object[] { id, ten, id_tinh }, "@id_huyen", "PHUYEN_NH")[0].ToString();
    }
    //Xóa huyện
    public static void P_HUYEN_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PHUYEN_XOA");
    }

    #endregion
    #region Tỉnh
    //Thêm mơi
    public static void P_TINH_NH(string id, string ten)
    {
        SqlData.ExeCuteProcedure(new object[] { id, ten }, "PTINH_NH");
    }
    public static void P_TINH_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PTINH_XOA");
    }
    #endregion

    #region Khối thi

    public static DataTable Fdt_KHOI_THI_LKE()
    {
        return SqlData.GetData("PKHOI_THI_LKE");
    }

    public static string Fdt_KHOI_THI_NH(string id, string khoi, string mon, float hso)
    {
        return SqlData.ExeCuteProcedure(new object[] { id, khoi, mon, hso }, "@Id", "PKHOI_THI_NH")[0].ToString();
    }
    public static void Fdt_KHOI_THI_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "PKHOI_THI_XOA");
    }
    public static DataTable Fdt_KHOI_THI_LKE_DROP()
    {
        return SqlData.GetData("PKHOI_THI_LKE_DROP");
    }
    #endregion

    #region Tuyển sinh
    //Đào tạo THPT
    public static DataSet Fds_TUYEN_SINH_LKE(string id_phieu)
    {
        return SqlData.GetMutilData(new object[] { id_phieu }, "PTUYEN_SINH_LKE");
    }
    public static void P_TUYEN_SINH_NH(ref string id_dk, string so_phieu, string khoi, string ma_nganh, string ten_nganh, string ten_cnganh,
                  string ma_truongnv, string ten_truongnv, string ma_ng_nv, string ten_ngnv, string ten_cnnv, string nam_ts,
                    string hodem, string ten, string anh, string ngaysinh, string ton_giao, string noisinh, string gt, string dt, string id_dt, string cmt, string ma_vung, string ttru,
                        string kv, string namTn, string noinop, string dvi_dk, string lienhe, string sdt, DataTable b_dt_pt)
    {
        LIB.Core.luu.ht_nsd _nsd = new LIB.Core.luu.ht_nsd();
        int k;
        if (!int.TryParse(namTn, out k)) throw new Exception("loi:năm tốt nghiệp là kiểu số:loi");
        object[] pram = new object[] {id_dk, so_phieu, khoi, ma_nganh, ten_nganh, ten_cnganh, ma_truongnv, ten_truongnv, 
                ma_ng_nv, ten_ngnv, ten_cnnv, nam_ts, hodem,  ten, anh, ngaysinh, ton_giao, noisinh, gt, dt, id_dt, cmt,  ma_vung, ttru, kv,
                        namTn, noinop, dvi_dk, lienhe, sdt, _nsd.user};
        object[] b_so_id = SqlData.ExeCuteProcedure(pram, "@id_dk", "PTUYEN_SINH_NH");
        id_dk = LIB.Core.LibConvert.toString(b_so_id[0]);
        LIB.Core.LibTable.ReplaceValue(ref b_dt_pt, "phieu", id_dk);
        SqlData.ExeCuteProcedure(b_dt_pt, "PQTHT_NH");
    }

    public static DataTable Fdt_TS_TIM(string b_nam_ts, string b_nganh, string b_nganh_xt, string b_g, string khoi)
    {
        return SqlData.GetData(new object[] { b_nam_ts, b_nganh, b_nganh_xt, b_g, khoi }, "PTUYEN_SINH_TIM");
    }

    public static void P_SAP_SO_BD(string nam, int so_sv, string sbd, string phong)
    {
        SqlData.ExeCuteProcedure(new object[] { nam, so_sv, sbd, phong }, "PTUYEN_SINH_DANHSO");
    }
    #endregion
    #region Đối tương
    public static DataTable Fdt_DOI_TUONG_LKE()
    {
        return SqlData.GetData("PDOI_TUONG_LKE");
    }
    #endregion

    #region Khuc vưc uue tiên
    public static DataTable Fdt_KHU_VUC_LKE()
    {
        return SqlData.GetData("PKHU_VUC_LKE");
    }
    #endregion
    public static DataSet Fs_DIEM_MON_HK(string nam, string mon, string hk)
    {
        try
        {
            DataSet set = new DataSet();
            DataTable b_dt = SqlData.GetData(new object[] { nam, mon, hk }, "PTHONG_KE_DIEM_MON");
            DataTable b_dt_tso = new DataTable();
            LIB.Core.LibTable.AddNewCol(ref b_dt_tso, new string[] { "url", "ten" });
            LIB.Core.LibTable.AddNewRow(ref b_dt_tso, new string[] { "~/App_rpt/", "r_bc_diem_mon" });

            set.Tables.Add(b_dt.Copy());
            b_dt_tso.TableName = "TENBC";
            DataTable b_tso = new DataTable("TSO");
            set.Tables.Add(b_dt_tso.Copy());
            set.Tables.Add(b_tso.Copy());
            return set;
        }
        catch (Exception ex)
        {
            return new DataSet();
        }

    }
    public static DataSet Fs_DANH_SACH_HBONG(string nam, string lop, string b_ten, string hk)
    {
        try
        {
            DataSet set = new DataSet();
            DataTable b_dt = new DataTable();
            b_dt = SqlData.GetData(new object[] { nam, lop, hk }, "PXET_HOC_BONG_TIM");
            if (b_dt.Rows.Count == 0) throw new Exception("Không có số liệu");
            DataTable b_dt_tso = new DataTable();
            LIB.Core.LibTable.AddNewCol(ref b_dt_tso, new string[] { "url", "ten" });
            LIB.Core.LibTable.AddNewRow(ref b_dt_tso, new string[] { "~/App_rpt/", "r_bc_ds_hbong" });

            set.Tables.Add(b_dt.Copy());
            b_dt_tso.TableName = "TENBC";
            DataTable b_tso = new DataTable("TSO");
            LIB.Core.LibTable.AddNewCol(ref b_tso, "ten");
            string b_tde = string.Empty;
            b_tde = (lop == "" ? "danh sách toàn trường" : "danh sách lớp " + b_ten);
            LIB.Core.LibTable.AddNewRow(ref b_tso, b_tde);
            set.Tables.Add(b_dt_tso.Copy());
            set.Tables.Add(b_tso.Copy());
            return set;
        }
        catch (Exception ex)
        {
            return new DataSet();
        }

    }
    public static DataSet Fds_BANG_DIEM_CT(string nam, string ma_sv, string hk, string b_ten)
    {
        DataSet set = new DataSet();
        DataTable b_dt = new DataTable();
        DataSet b_ds = new DataSet();
        b_ds = SqlData.GetMutilData(new object[] { nam, ma_sv, hk }, "PDEM_CT_HOC_SINH");
        b_dt = b_ds.Tables[0];
        if (b_dt.Rows.Count == 0) throw new Exception("Không có số liệu");
        DataTable b_dt_tso = new DataTable();
        LIB.Core.LibTable.AddNewCol(ref b_dt_tso, new string[] { "url", "ten" });
        LIB.Core.LibTable.AddNewRow(ref b_dt_tso, new string[] { "~/App_rpt/", "r_bc_diem_ct" });

        set.Tables.Add(b_dt.Copy());
        b_dt_tso.TableName = "TENBC";
        DataTable b_tso = new DataTable("TSO");
        LIB.Core.LibTable.AddNewCol(ref b_tso, "ten");
        LIB.Core.LibTable.AddNewRow(ref b_tso, b_ten.ToUpper());
        foreach (DataRow b_dr in b_ds.Tables[1].Rows)
        {
            LIB.Core.LibTable.AddNewRow(ref b_tso, LIB.Core.LibConvert.toString(b_dr["ten"]));
            LIB.Core.LibTable.AddNewRow(ref b_tso, LIB.Core.LibConvert.toString(b_dr["ten_lop"]));
        }
        set.Tables.Add(b_dt_tso.Copy());
        set.Tables.Add(b_tso.Copy());
        return set;
    }
    #region Công văn
    // Loại công văn
    public static string P_LCV_NH(string id, string ten, string gc)
    {
        return SqlData.ExeCuteProcedure(new object[] { id, ten, gc }, "@ma", "P_LCV_NH")[0].ToString();
    }
    public static void P_LCV_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "P_LCV_XOA");
    }
    //---- Chủ đề công văn ---
    public static string P_CDCV_NH(string id, string ten, string gc)
    {
        return SqlData.ExeCuteProcedure(new object[] { id, ten, gc }, "@ma", "P_CDCV_NH")[0].ToString();
    }
    public static void P_CDCV_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "P_CDCV_XOA");
    }
    //---- Tình trạng ---
    public static string P_TINH_TRANG_NH(string id, string ten, string gc)
    {
        return SqlData.ExeCuteProcedure(new object[] { id, ten, gc }, "@ma", "P_TINH_TRANG_NH")[0].ToString();
    }
    public static void P_TINH_TRANG_XOA(string id)
    {
        SqlData.ExeCuteProcedure(id, "P_TINH_TRANG_XOA");
    }

    #endregion

    public static DataSet Fds_NHAN_SU_TKHAI(string b_ma_cb)
    {
        try
        {
            DataSet set = new DataSet();
            DataSet b_ds = SqlData.GetMutilData(new object[] { b_ma_cb }, "PNHAN_SU_BAOCAO");
            DataTable b_dt_tso = new DataTable();
            LIB.Core.LibTable.AddNewCol(ref b_dt_tso, new string[] { "url", "ten" });
            LIB.Core.LibTable.AddNewRow(ref b_dt_tso, new string[] { "~/App_rpt/", "r_hso_tkhai" });
            LIB.Core.LibTable.AddNewRow(ref b_dt_tso, new string[] { "", "r_qh_gd" });

            set.Tables.Add(b_ds.Tables[0].Copy());
            DataTable tso = b_ds.Tables[1];
            tso.TableName = "r_qh_gd";
            b_dt_tso.TableName = "TENBC";
            DataTable b_tso = new DataTable("TSO");
            set.Tables.Add(b_dt_tso.Copy());
            set.Tables.Add(b_tso.Copy());
            return set;
        }
        catch (Exception ex)
        {
            return new DataSet();
        }
    }
    
    //tungns
    public static DataTable Fdt_LKE_DSDANGKY_HOC(string b_ma_sv, string b_namhoc, string b_hk)
    {
        return SqlData.GetData(new object[] { b_ma_sv, b_namhoc, b_hk }, "PDKYHOC_LKE");
    }

    //Tuyển sinh
    public static void PTT_TUYEN_SINH_NH(ref string id_ts, string nam_ts, string nganh_ts, string khoi, int sl, float diem, string nsd)
    {
        SqlData.ExeCuteProcedure(new object[] { id_ts, nam_ts, nganh_ts, khoi, sl, diem, nsd }, "@id_ts", "PXET_TUYEN_NH"); 
    }
}